package com.ismartv.sale.dao;

import com.ismartv.sale.bean.UserInfo;

import java.sql.*;
import java.util.*;

public class CommonDao {

	private Connection conn;
	private static final String usertable = "userInfo";

	public CommonDao() throws DaoException {
		this.conn = ConnectionManager.getConnection();
	}

	public void begin() throws DaoException {
		if (conn != null) {
			try {
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				throw new DaoException("can not begin transaction", e);
			}
		} else {
			throw new DaoException("connection not opened!");
		}
	}

	public void commit() throws DaoException {
		try {
			if (conn != null && !conn.getAutoCommit()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				if (conn == null) {
					throw new DaoException("connection not opened!");
				} else {
					throw new DaoException("first begin then commit please!");
				}
			}
		} catch (SQLException e) {
			throw new DaoException("can not commit transaction!", e);
		}
	}

	public void rollback() throws DaoException {
		try {
			if (conn != null && !conn.getAutoCommit()) {
				conn.rollback();
				conn.setAutoCommit(true);
			} else {
				if (conn == null) {
					throw new DaoException("connection not opened!");
				} else {
					throw new DaoException("first begin then rollback please!");
				}
			}
		} catch (SQLException e) {
			throw new DaoException("can not rollback transaction!", e);
		}
	}

	private List<Object> convert(ResultSet rs) throws DaoException {

		// record list
		List<Object> retList = new ArrayList<Object>();

		try {
			ResultSetMetaData meta = rs.getMetaData();

			// column count
			int colCount = meta.getColumnCount();

			// each record
			while (rs.next()) {

				Map<String, Object> recordMap = new HashMap<String, Object>();

				// each column
				for (int i = 1; i <= colCount; i++) {
					// column name
					String name = meta.getColumnName(i);
					// column value
					Object value = rs.getObject(i);
					// add column to record
					recordMap.put(name, value);
				}
				// ad record to list
				retList.add(recordMap);
			}
		} catch (SQLException ex) {
			throw new DaoException("can not convert result set to list of map",
					ex);
		}
		return retList;
	}

	private void apply(PreparedStatement pstmt, List<Object> params)
			throws DaoException {
		try {
			// if params exist
			if (params != null && params.size() > 0) {
				// parameters iterator
				Iterator<Object> it = params.iterator();

				// parameter index
				int index = 1;
				while (it.hasNext()) {

					Object obj = it.next();
					// if null set ""
					if (obj == null) {
						pstmt.setObject(index, "");
					} else {
						// else set object
						pstmt.setObject(index, obj);
					}

					// next index
					index++;
				}
			}
		} catch (SQLException ex) {
			throw new DaoException("can not apply parameter", ex);
		}
	}

	public List<Object> query(String sql, List<Object> params)
			throws DaoException {
		List<Object> result = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			this.apply(pstmt, params);
			rs = pstmt.executeQuery();
			result = this.convert(rs);
		} catch (SQLException ex) {
			throw new DaoException("can not execute query", ex);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}

	public Map<String, Object> queryOne(String sql, List<Object> params)
			throws DaoException {
		List<Object> list = this.query(sql, params);

		if (list == null || list.size() == 0) {
			throw new DaoException("data not exist");
		} else {
			@SuppressWarnings("unchecked")
			Map<String, Object> record = (Map<String, Object>) list.get(0);
			if (record == null || record.size() == 0) {
				throw new DaoException("data not exist");
			} else {
				return record;
			}
		}
	}

	public int execute(String sql, List<Object> params) throws DaoException {
		int ret = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			this.apply(pstmt, params);
			ret = pstmt.executeUpdate();
		} catch (SQLException ex) {
			throw new DaoException("", ex);
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
				}
			}
		}
		return ret;
	}

	public List<Object>[] queryBatch(String[] sqlArray,
			List<Object>[] paramArray) throws DaoException {
		List<Object> rets = new ArrayList<Object>();
		if (sqlArray.length != paramArray.length) {
			throw new DaoException("sql size not equal parameter size");
		} else {
			for (int i = 0; i < sqlArray.length; i++) {
				String sql = sqlArray[i];
				List<Object> param = paramArray[i];
				List<Object> ret = this.query(sql, param);
				rets.add(ret);
			}
			return (List[]) rets.toArray();
		}
	}

	public int[] executeBatch(String[] sqlArray, List[] paramArray)
			throws DaoException {
		List rets = new ArrayList();
		if (sqlArray.length != paramArray.length) {
			throw new DaoException("sql size not equal parameter size");
		} else {
			for (int i = 0; i < sqlArray.length; i++) {
				int ret = this.execute(sqlArray[i], paramArray[i]);
				rets.add(new Integer(ret));
			}

			int[] retArray = new int[rets.size()];
			for (int i = 0; i < retArray.length; i++) {
				retArray[i] = ((Integer) rets.get(i)).intValue();
			}

			return retArray;
		}
	}

	public void close() throws DaoException {
		try {
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			} else {
				if (conn == null) {
					throw new DaoException(
							"can not close null connection, first new then close");
				} else {
					throw new DaoException(
							"transaction is running, rollbakc or commit befor close please.");
				}
			}
		} catch (SQLException ex) {
			throw new DaoException("Can not close common dao");
		}
	}

	public UserInfo getUserInfo(String userName, String password) {
		ArrayList<Object> paras = new ArrayList<Object>();
		paras.add(userName);
		paras.add(password);
		UserInfo user = new UserInfo();
		StringBuffer query = new StringBuffer();
		query.append("select * from " + usertable)
				.append(" where userName = ? and ").append("password = ?");
		try {
			Map<String, Object> result = this.queryOne(query.toString(), paras);
			user.setUserName((String) result.get("userName"));
			user.setPrivilegeCode((Integer) result.get("privilegeCode"));
			user.setUserName((String) result.get(""));
		} catch (DaoException e) {
			e.printStackTrace();
			return null;
		}
		return user;
	}
}
